package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.rowset.CachedRowSet;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.log4j.Logger;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.myride.Utils;
import com.sun.rowset.CachedRowSetImpl;

public class DBUtils {
	public static final String DEFAULT_PROPERTIES_FILE = "myride.properties";
	public static final String DEFAULT_LOG4J_FILE = "myride.log4j.properties";
	public static final int INT_NOT_FOUND = -1;
	public static final String STRING_NOT_FOUND = "NOT_FOUND";
	private static final int MAX_TRIES_TO_ESTABLISH_CONNECTION = 10;
	
	static Map<String, String> spParams;
	public static Logger logger;	
	
	static {								
		try {	
			logger = Logger.getRootLogger();
			initDBPool();
			
			spParams = new HashMap<String, String>();
			
			spParams.put("sp_GetUser", ":user_id");
			spParams.put("sp_GetUserByOpensocialId", ":opensocial_id");
			spParams.put("sp_UpdateUser", ":user_id, :first_name, :last_name, :city, :street, :house_num, :faculty, :hobbies, :participation, :lon, :lat, :image_url, :email");
			spParams.put("sp_GetUsers", "");
			spParams.put("sp_SetUserSchedule", ":uid, :day, :dest, :role, :time");
			spParams.put("sp_GetUserSchedule", ":uid");
			spParams.put("sp_SearchRides", ":uid, :day, :radius, :timeframe");
			spParams.put("sp_GetThisWeek", ":uid, :week_num");
			spParams.put("sp_GetRoles", "");			
			spParams.put("sp_AddRequest", ":seeker, :dest, :day, :carOwner, :text");
			spParams.put("sp_GetRequests", ":uid");
			spParams.put("sp_DenyRequest", ":ride_id, :passenger_id");
			spParams.put("sp_ApproveRequest", ":ride_id, :passenger_id");
			spParams.put("sp_GetRequests", ":uid");
			spParams.put("sp_GetCurrentWeekDates", ":week_num");
			spParams.put("sp_GetCountOfWaitingRequests", ":uid, :week_num");
			
			
			
		} catch (Exception e) {			
			e.printStackTrace();
		}
	}			
		
	private static void initDBPool() throws SQLException, ClassNotFoundException, Exception
	{
		logger.debug("Starting DB Pool...");
		
		try {
			String connectionUrl = Utils.getProperty("connection.string");			
			Class.forName("com.mysql.jdbc.Driver"); 
			GenericObjectPool connectionPool = new GenericObjectPool(null);
			connectionPool.setMaxActive(30);
			connectionPool.setMaxIdle(10);
			connectionPool.setMaxWait(5000);
			connectionPool.setWhenExhaustedAction(GenericObjectPool.WHEN_EXHAUSTED_GROW);
			ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectionUrl,null,null);
	        @SuppressWarnings("unused")
			PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);
	        Class.forName("org.apache.commons.dbcp.PoolingDriver");
	        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
	        driver.registerPool("auxdbpoll",connectionPool);
	        
	        // Loop until a DB connection can be established to make sure DB is up
	        Connection conn = null;
	        for(int i = 0; i < MAX_TRIES_TO_ESTABLISH_CONNECTION; i++) {
	        	try {
	        		conn = getDBConnection();
	        		i = MAX_TRIES_TO_ESTABLISH_CONNECTION;
	        	} catch(Exception sqe) {
	        		logger.info("initDBPool: Exception while trying to get Connection. Sleeping 5 seconds");
	        		Thread.sleep(5000);
	        	} finally {
	        		if(conn != null) {
	        			conn.close();
	        		}
	        	}
	        }
		}
		catch (Exception e) {
			logger.error("initDBPool: Exception caught.", e);
		}		                
	}
	
	public static Connection getDBConnection() throws SQLException {
		return DriverManager.getConnection("jdbc:apache:commons:dbcp:auxdbpoll");
	}

	
	public static CachedRowSet execStmt(String cmd, Object param) {				
		Connection conn = null;
		PreparedStatement pstmt = null;		
		ResultSet rs = null;		
		CachedRowSet csrt = null;
		
		try {						
			conn = getDBConnection();			
			pstmt = conn.prepareStatement(cmd);						
			pstmt.setObject(1, param);						
			
			rs = pstmt.executeQuery();						
			csrt = new CachedRowSetImpl();
			csrt.populate(rs);									
		}
		catch (Exception e) {
			return null;				
		}		
		finally {			
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (Exception e) {
				return null;
			}
		}	
		
		return csrt;	
	}
	
	public static CachedRowSet execStmt(String cmd, Object[] params) {				
		Connection conn = null;
		PreparedStatement pstmt = null;		
		ResultSet rs = null;		
		CachedRowSet csrt = null;
		
		try {						
			conn = getDBConnection();			
			pstmt = conn.prepareStatement(cmd);						
			
			if (params != null) {
				for (int i = 1; i <= params.length; i++) {
					pstmt.setObject(i, params[i]);				
				}						
			}
			rs = pstmt.executeQuery();						
			csrt = new CachedRowSetImpl();
			csrt.populate(rs);									
		}
		catch (Exception e) {
			return null;				
		}		
		finally {			
			try {
				rs.close();
				pstmt.close();
				conn.close();
			} catch (Exception e) {
				return null;
			}
		}	
		
		return csrt;	
	}
	
	public static JSONObject toJSON(ResultSet rs) throws JSONException {
		JSONObject json = new JSONObject();
		JSONArray rows = new JSONArray();
		
		try {
			
		    ResultSetMetaData metaData = rs.getMetaData();

		    while (rs.next()) {
		        JSONObject entry = new JSONObject();		        
		        for (int i=1; i<=metaData.getColumnCount(); ++i) {
		            entry.put(metaData.getColumnLabel(i), rs.getObject(i));
		        }
		        rows.put(entry);
		    }
			
			json.put("rows", rows);
			json.put("status", "success");
			
			return json;			
		}
		catch (Exception e) {			
			json.put("status", "failure");						
		}
		
		return json;
	}
		
	public static String getSpParams(String sp) {
		return spParams.get(sp);
	}
}
